﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using iTFF.WPF.CP;
using iTFF.WPF.Resources.Languages;
using iTFF.WPF.Model.Lyrics.LocalProviders.SQLCe.Queries;
using iTFF.WPF.SQLCE;

namespace iTFF.WPF.Model.Lyrics.LocalProviders
{
    class SQLCeLocalProvider : ILyricsLocalProvider
    {

        #region Fields

        iTunesControlPanel _itcp;
        LyricsControlPanel _lcp;

        #endregion

        #region Constructor

        public SQLCeLocalProvider()
        {
            _itcp = ControlPanel.Instance.iTunesControlPanel;
            _lcp = ControlPanel.Instance.LyricsControlPanel;
        }

        #endregion

        #region ILyricsLocalProvider Members

        public string DisplayName
        {
            get { return "SQL Server Compact Edition Database"; }
        }

        /// <summary>
        /// Gets the lyrics from the database, if existing
        /// </summary>
        /// <param name="persistentID"></param>
        /// <returns></returns>
        /// 
        public LyricsModel GetLyrics(ulong persistentID)
        {

            // get the hex
            string hexid = _itcp.GetHexRepresentationFromPersistentID(persistentID);

            try
            {

                using (SqlCeConnection conn = new SqlCeConnection(ControlPanel.Instance.SQLCeDatabaseConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand(Queries.GetTrackLyrics);
                    cmd.Connection = conn;

                    // Create provider specific parameters
                    cmd.Parameters.Add(new SqlCeParameter("@TrackPersistentHEX", SqlDbType.NChar, 16) { Value = hexid });
                    cmd.Connection.Open();
                    var results = cmd.ExecuteReader();

                    DataTable dt = new DataTable();
                    dt.Load(results);

                    if (dt.Rows.Count > 0)
                    {
                        var row = dt.AsEnumerable().First();

                        var model = new LyricsModel()
                        {
                            iTunesPersistentID = persistentID,
                            Album = (string)row["Album"],
                            Artist = (string)row["Artist"],
                            Name = (string)row["Name"],
                            Provider = _lcp.GetWebProvider((string)row["LyricsWebProviderName"]),
                            Lyrics = (string)row["Lyrics"]
                        };

                        return model;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format(Strings.SQLCeException, ex.Message));
            }

        }

        /// <summary>
        /// Saves the lyrics in the SQL CE DB
        /// </summary>
        /// <param name="persistentID"></param>
        /// <param name="force"></param>
        /// <param name="lyrics"></param>
        /// <returns></returns>
        public bool SaveLyrics(LyricsModel model, bool force)
        {
            // get the hex
            string hexid = _itcp.GetHexRepresentationFromPersistentID(model.iTunesPersistentID);

            // first, check if the lyrics are existing
            var exists = this.CheckTrackExists(model.iTunesPersistentID);
            if (force || (!force && !exists))
                return InsertOrUpdateTrack(model, !exists);
            else
                return false;
        }

        /// <summary>
        /// Removes the track from the db
        /// </summary>
        /// <param name="persistentID"></param>
        /// <returns></returns>
        public bool RemoveLyrics(ulong persistentID)
        {

            // get the hex
            string hexid = _itcp.GetHexRepresentationFromPersistentID(persistentID);

            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(ControlPanel.Instance.SQLCeDatabaseConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand(Queries.DeleteTrack);
                    cmd.Connection = conn;
                    cmd.Parameters.Add(new SqlCeParameter("@TrackPersistentHEX", SqlDbType.NChar, 16) { Value = hexid });
                    cmd.Connection.Open();
                    int res = cmd.ExecuteNonQuery();

                    return res > 0;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format(Strings.SQLCeException, ex.Message));
            }

        }

        /// <summary>
        /// Checks if the track is in the db and if the lyrics isn't empty
        /// </summary>
        /// <param name="persistentID"></param>
        /// <returns></returns>
        public bool LyricsExists(ulong persistentID)
        {

            // get the hex
            string hexid = _itcp.GetHexRepresentationFromPersistentID(persistentID);

            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(ControlPanel.Instance.SQLCeDatabaseConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand(Queries.GetTrackLyrics);
                    cmd.Connection = conn;
                    cmd.Parameters.Add(new SqlCeParameter("@TrackPersistentHEX", SqlDbType.NChar, 16) { Value = hexid });
                    cmd.Connection.Open();
                    var res = cmd.ExecuteReader();

                    var dt = new DataTable();
                    dt.Load(res);

                    if (dt.Rows.Count > 0)
                    {
                        var lyrics = (string)dt.AsEnumerable().First()["Lyrics"];
                        return !string.IsNullOrEmpty(lyrics);
                    }
                    else
                        return false;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format(Strings.SQLCeException, ex.Message));
            }

        }

        public int Metric
        {
            get { return 10; }
        }

        public bool IsValid
        {
            get { return new DatabaseUtils().VerifyDatabase(); }
        }

        public bool IsActivated { get; set; }

        #endregion

        #region Private methods

        /// <summary>
        /// Checks if the track row exists in the DB
        /// </summary>
        /// <param name="persistentID"></param>
        /// <returns></returns>
        private bool CheckTrackExists(ulong persistentID)
        {

            // get the hex
            string hexid = _itcp.GetHexRepresentationFromPersistentID(persistentID);

            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(ControlPanel.Instance.SQLCeDatabaseConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand(Queries.CheckTrackExists);
                    cmd.Connection = conn;
                    cmd.Parameters.Add(new SqlCeParameter("@TrackPersistentHEX", SqlDbType.NChar, 16) { Value = hexid });
                    cmd.Connection.Open();
                    int res = (int)cmd.ExecuteScalar();

                    return res >= 1;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format(Strings.SQLCeException, ex.Message));
            }

        }

        /// <summary>
        /// Inserts or updates a track in the sql db
        /// </summary>
        /// <param name="lyricsModel"></param>
        /// <param name="insert"></param>
        /// <returns></returns>
        private bool InsertOrUpdateTrack(LyricsModel lyricsModel, bool insert)
        {
            var hexid = _itcp.GetHexRepresentationFromPersistentID(lyricsModel.iTunesPersistentID);

            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(ControlPanel.Instance.SQLCeDatabaseConnectionString))
                {
                    SqlCeCommand cmd;
                    if (insert)
                        cmd = new SqlCeCommand(Queries.InsertTrackLyrics);
                    else
                        cmd = new SqlCeCommand(Queries.UpdateTrackLyrics);

                    cmd.Connection = conn;

                    // Create provider specific parameters
                    cmd.Parameters.Add(new SqlCeParameter("@TrackPersistentHEX", SqlDbType.NChar, 16) { Value = hexid });
                    cmd.Parameters.Add(new SqlCeParameter("@Name", SqlDbType.NVarChar, lyricsModel.Name.Length) { Value = lyricsModel.Name });
                    cmd.Parameters.Add(new SqlCeParameter("@Artist", SqlDbType.NVarChar, lyricsModel.Artist.Length) { Value = lyricsModel.Artist });
                    cmd.Parameters.Add(new SqlCeParameter("@Album", SqlDbType.NVarChar, lyricsModel.Album.Length) { Value = lyricsModel.Album });
                    cmd.Parameters.Add(new SqlCeParameter("@Lyrics", SqlDbType.NVarChar, lyricsModel.Lyrics.Length) { Value = lyricsModel.Lyrics });
                    cmd.Parameters.Add(new SqlCeParameter("@LyricsWebProviderName", SqlDbType.NVarChar, lyricsModel.Provider.Name.Length) { Value = lyricsModel.Provider.Name });

                    cmd.Connection.Open();
                    int results = cmd.ExecuteNonQuery();

                    return results == 1;
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException(string.Format(Strings.SQLCeException, ex.Message));
            }
        }

        #endregion
    }
}
